package guoanmaker.provider.business.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import guoanmaker.provider.business.modelnobind.PersonalFinanceProjectForAPPPerson;
import guoanmaker.provider.business.modelnobind.PersonalFinanceProjectForAPPVisitor;
import guoanmaker.provider.business.modelnobind.PersonalFinanceProjectForPCPerson;
import guoanmaker.provider.business.modelnobind.PersonalFinanceProjectForPCVisitor;

/**
 * 金融服务Dao层
 * 2017-12-19
 * @author ws
 *
 */

@Repository
public class PersonalFinanceProjectDao {
	
	@PersistenceContext
	EntityManager em;
	
	/**
	 * 分页查询PC端个人中心已发布金融服务VO，按审核通过日期降序排序
	 * @param userId
	 * 			用户id，为发布项目者id即登录网站的用户id
	 * @param page
	 * 			当前页数,从0开始
	 * @param size
	 * 			每页可显示VO数量
	 * @return
	 */
	public List<PersonalFinanceProjectForPCPerson> findPersonalFinanceProjectForPCPerson(String userId, Integer page, Integer size){
		String jpql = "select new guoanmaker.provider.business.modelnobind.PersonalFinanceProjectForPCPerson("
				+" pfp.id, pfp.toExamineTime, pfp.name, pfp.status)"
				+" from PersonalFinanceProject pfp"
				+" where pfp.personalUserId = :userId"
				+" order by pfp.status ASC, pfp.toExamineTime DESC, pfp.updateTime DESC";
		Query query = em.createQuery(jpql);
		query.setParameter("userId", userId);
		query.setFirstResult(page * size);
		query.setMaxResults(size);
		List<PersonalFinanceProjectForPCPerson> list = query.getResultList();
		
		if(list!=null && list.size()>0){
			return list;
		}
		return null;
	}
	
	/**
	 * 根据金融项目id查询PC端游客展示的金融项目VO
	 * @param projectId
	 * 			金融项目id
	 * @return
	 */
	public PersonalFinanceProjectForPCVisitor findPersonalFinanceProjectForPCVisitorById(String projectId){
		if(projectId == null || projectId.trim().length() == 0){
			return null;
		}
		
		String jpql = "select new guoanmaker.provider.business.modelnobind.PersonalFinanceProjectForPCVisitor("
				+ "	pfp.id, pfp.name, pfp.projectPhoto, pfp.toExamineTime, pfp.financingTarget, pfppl.name, pfp.roundOfFinancing, pfp.industryField, pfp.summary, pfp.useOfFunds)"
				+ " from PersonalFinanceProject pfp left join pfp.personalFinanceProjectPartnerList pfppl"
				+ " where pfp.status = 0 and pfppl.flag = 0 and pfp.id = :projectId";
		
		Query query = em.createQuery(jpql);
		query.setParameter("projectId", projectId);
		
		return (PersonalFinanceProjectForPCVisitor)query.getSingleResult();
	}	
	/**
	 * 分页查询PC端游客浏览金融服务VO，金融服务必须是已通过项目
	 * @param industryField
	 * 			所属行业
	 * @param financingTargetBegin
	 * 			融资目标起始金额
	 * @param financingTargetEnd
	 * 			融资目标终止金额
	 * @param sortBy
	 * 			排序依赖     "toExamineTime"审核通过时间     "financingTarget"融资目标
	 * @param strategy
	 * 			排序规则     "DESC"降序排序    "ASC"升序排序
	 * @param page
	 * 			当前页数,从0开始
	 * @param size
	 * 			每页可显示数量
	 * @return
	 */
	public List<PersonalFinanceProjectForPCVisitor> findPersonalFinanceProjectForPCVisitor(String industryField, BigDecimal financingTargetBegin, BigDecimal financingTargetEnd, String sortBy, String strategy, Integer page, Integer size){
		if(page == null || size == null){
			return null;
		}
		
		String jpql = "select new guoanmaker.provider.business.modelnobind.PersonalFinanceProjectForPCVisitor("
				+ "	pfp.id, pfp.name, pfp.projectPhoto, pfp.toExamineTime, pfp.financingTarget, pfppl.name, pfp.roundOfFinancing, pfp.industryField, pfp.summary, pfp.useOfFunds)"
				+ " from PersonalFinanceProject pfp left join pfp.personalFinanceProjectPartnerList pfppl"
				+ " where pfp.status = 0 and pfppl.flag = 0";
		
		if(industryField != null && industryField.trim().length() != 0){
			jpql += " and pfp.industryField = :industryField";
		}
		if(financingTargetBegin != null){
			jpql += " and pfp.financingTarget >= :financingTargetBegin";
		}
		if(financingTargetEnd != null){
			jpql += " and pfp.financingTarget <= :financingTargetEnd";
		}
		
		//设定排序规则
		if(sortBy != null && sortBy.equals("toExamineTime")){
			if(strategy != null && strategy.equals("DESC")){
				jpql += " order by pfp.toExamineTime DESC, pfp.updateTime DESC";
			}else if(strategy != null && strategy.equals("ASC")){
				jpql += " order by pfp.toExamineTime ASC, pfp.updateTime DESC";
			}
		}else if(sortBy != null && sortBy.equals("financingTarget")){
			if(strategy != null && strategy.equals("DESC")){
				jpql += " order by pfp.financingTarget DESC, pfp.updateTime DESC";
			}else if(strategy != null && strategy.equals("ASC")){
				jpql += " order by pfp.financingTarget ASC, pfp.updateTime DESC";
			}
		}
		
		Query query = em.createQuery(jpql);
		if(industryField != null && industryField.trim().length() != 0){
			query.setParameter("industryField", industryField);
		}
		if(financingTargetBegin != null){
			query.setParameter("financingTargetBegin", financingTargetBegin);
		}
		if(financingTargetEnd != null){
			query.setParameter("financingTargetEnd", financingTargetEnd);
		}
	
		query.setFirstResult(page*size);
		query.setMaxResults(size);
		List<PersonalFinanceProjectForPCVisitor> list = query.getResultList();
		return list;
	}
	
	/**
	 * 查询符合条件并且已通过审核的项目数量
	 * @param industryField
	 * 			所属行业
	 * @param financingTargetBegin
	 * 			融资目标起始金额
	 * @param financingTargetEnd
	 * 			融资目标终止金额
	 * @return
	 */
	public long findTotalByIndustryFieldAndFinancingTarget(String industryField, BigDecimal financingTargetBegin, BigDecimal financingTargetEnd){
		String jpql = "select count(pfp.id)"
				+ " from PersonalFinanceProject pfp left join pfp.personalFinanceProjectPartnerList pfppl"
				+ " where pfp.status = 0 and pfppl.flag = 0";
		
		if(industryField != null && industryField.trim().length() != 0){
			jpql += " and pfp.industryField = :industryField";
		}
		if(financingTargetBegin != null){
			jpql += " and pfp.financingTarget >= :financingTargetBegin";
		}
		if(financingTargetEnd != null){
			jpql += " and pfp.financingTarget <= :financingTargetEnd";
		}
		
		Query query = em.createQuery(jpql);
		if(industryField != null && industryField.trim().length() != 0){
			query.setParameter("industryField", industryField);
		}
		if(financingTargetBegin != null){
			query.setParameter("financingTargetBegin", financingTargetBegin);
		}
		if(financingTargetEnd != null){
			query.setParameter("financingTargetEnd", financingTargetEnd);
		}
		
		Long count = (Long)query.getSingleResult();
		return count==null?0:count;
	}
	
	/**
	 * 分页查询APP端个人中心已发布金融服务VO,按更新时间降序
	 * @param userId
	 * 			用户id
	 * @param status
	 * 			金融服务项目状态   （0:已通过   1:已下线   2:已拒绝   3:待发布   4:待审核）
	 * @param page
	 * 			当前页数
	 * @param size
	 * 			每页可显示数量
	 * @return
	 */
	public List<PersonalFinanceProjectForAPPPerson> findPersonalFinanceProjectForAPPPerson(String userId, Integer status, Integer page, Integer size){
		if(page == null || size == null){
			return null;
		}
		
		String jpql = "select new guoanmaker.provider.business.modelnobind.PersonalFinanceProjectForAPPPerson("
				+ " id, name, summary, roundOfFinancing, financingTarget, toExamineTime, updateTime, sellShares)"
				+ " from PersonalFinanceProject"
				+ " where personalUserId = :userId and status = :status"
				+ " order by updateTime DESC";
		
		Query query = em.createQuery(jpql);
		query.setParameter("userId", userId);
		query.setParameter("status", status);
		query.setFirstResult(page * size);
		query.setMaxResults(size);
		
		List<PersonalFinanceProjectForAPPPerson> list = query.getResultList();
		
		String jpql1 = "select url from t_personal_domain_name";
		Query query1 = em.createNativeQuery(jpql1);
		List<Object> urlList = query1.getResultList();
		if(urlList != null && urlList.size() > 0){
			String url = (String)urlList.get(0);
			url = url + "/h5/proDetali/detail.html?";
			for(PersonalFinanceProjectForAPPPerson temp : list){
				temp.setUrl(url + "userid=" + userId + "&id=" + temp.getId());
			}
		}
		
		return list;
	}
	
	/**
	 * 根据金融项目id查询APP端游客展示的金融项目VO
	 * @param projectId
	 *			金融项目id 
	 * @return
	 */
	public PersonalFinanceProjectForAPPVisitor findPersonalFinanceProjectForAPPVisitorById(String projectId){
		if(projectId == null || projectId.trim().length() == 0){
			return null;
		}
		
		String jpql = "select new guoanmaker.provider.business.modelnobind.PersonalFinanceProjectForAPPVisitor("
				+ " id, projectPhoto, name, summary, roundOfFinancing, financingTarget, toExamineTime, sellShares)"
				+ " from PersonalFinanceProject"
				+ " where status = 0 and id = :projectId";
				
		Query query = em.createQuery(jpql);
		query.setParameter("projectId", projectId);

		List<PersonalFinanceProjectForAPPVisitor> list = query.getResultList();
		if(list == null || list.size() == 0){
			return null;
		}
		return list.get(0);
	}
	
	/**
	 * 分页查询APP端游客浏览金融服务VO
	 * @param page
	 * @param size
	 * @return
	 */
	public List<PersonalFinanceProjectForAPPVisitor> findPersonalFinanceProjectForAPPVisitor(Integer page, Integer size){
		if(page == null || size == null){
			return null;
		}
		
		String jpql = "select new guoanmaker.provider.business.modelnobind.PersonalFinanceProjectForAPPVisitor("
				+ " id, projectPhoto, name, summary, roundOfFinancing, financingTarget, toExamineTime, sellShares)"
				+ " from PersonalFinanceProject"
				+ " where status = 0"
				+ " order by toExamineTime DESC, updateTime DESC";
		
		Query query = em.createQuery(jpql);
		query.setFirstResult(page * size);
		query.setMaxResults(size);
		
		List<PersonalFinanceProjectForAPPVisitor> list = query.getResultList();
		return list;
	}
	
	public String findRefuseReason(String projectId){
		if(projectId == null || projectId.trim().length() == 0){
			return null;
		}
		
		String jpql = "select a.finance_project_refuse, a.refuse_content"
				+ " from t_operator_approval_record a join t_personal_finance_project_copy b on a.finance_project_id = b.id join t_personal_finance_project c on b.project_old_id = c.id"
				+ " where a.approval_info = 0 and c.id = :projectId"
				+ " order by a.approval_time DESC";
		
		Query query = em.createNativeQuery(jpql);
		query.setParameter("projectId", projectId);
		List<Object[]> list = query.getResultList();
		
		if(list == null || list.size() == 0){
			return null;
		}
		
		String s1 = (String)list.get(0)[0];
		String s2 = (String)list.get(0)[1];
		s1 = s1==null?"":s1;
		s2 = s2==null?"":s2;
		
		return s1 + " " + s2;
	}
	
}
